package DB;

import Model.Student;
import Model.Team;

import java.sql.*;
import java.util.ArrayList;

public class StudentDB implements StudentDBIF {

    private Connection conn;
    private ConnectionDB connectionDB;

    public StudentDB () {
        conn = ConnectionDB.getInstance().getConn();
        connectionDB = ConnectionDB.getInstance();
    }

    @Override
    public int insertStudent(Student student) {
        int rc = -1;
        PreparedStatement pstmtStudent;
        PreparedStatement pstmtPerson;

        String insertStudent = "INSERT INTO Student (studentId, studentNo, teamId)" + "VALUES (?,?, ?)";
        String insertPerson = "INSERT INTO Person (id, name, username, password, address, email, phoneNo, type)" + "VALUES (?,?,?,?,?,?,?,?)";

        try {
            connectionDB.startTransaction();

            pstmtStudent = conn.prepareStatement(insertStudent);
            pstmtStudent.setInt(1, student.getStudentId());
            pstmtStudent.setInt(2, student.getStudentNo());
            pstmtStudent.setInt(3, student.getTeamId().getId());
            pstmtStudent.executeUpdate();

            pstmtPerson = conn.prepareStatement(insertPerson);
            pstmtPerson.setInt(1, student.getId());
            pstmtPerson.setString(2, student.getName());
            pstmtPerson.setString(3, student.getUsername());
            pstmtPerson.setString(4, student.getPassword());
            pstmtPerson.setString(5, student.getAddress());
            pstmtPerson.setString(6, student.getEmail());
            pstmtPerson.setInt(7, student.getPhoneNo());
            pstmtPerson.setString(8, student.getType());
            rc = pstmtPerson.executeUpdate();

            connectionDB.commitTransaction();
        }
        catch (Exception e) {
            System.out.println(e);
            connectionDB.rollbackTransaction();
        }

        return rc;
    }

    @Override
    public Student findStudentByID(int id) {
        return singleWhere("id = " + id + " AND studentId = " + id , false);
    }


    @Override
    public Student findStudent(String userName, String password) {
        return singleWhere("id = studentId AND username = '" + userName + "' AND password = '" + password + "'", false);
    }

    @Override
    public Student findStudentByStudentNo(int studentNo) {
        return singleWhere("id = studentId AND studentNo = " + studentNo, false);
    }

    @Override
    public ArrayList<Student> getAllStudents(boolean retrieveAssociation) {
        return miscWhere("id = studentId ", false);
    }

    @Override
    public ArrayList<Student> getStudentsByTeam(Team team, boolean retrieveAssociation) {
        return miscWhere(" teamId = " + team.getId() + " AND id = studentId", false);
    }

    @Override
    public int updateStudent(Student student) {
        Student studentObj = student;
        int rc = -1;

        String queryPerson = "UPDATE Person SET " +
                "name = '" + studentObj.getName() + "', " +
                "username = '" + studentObj.getUsername() + "', " +
                "password = '" + studentObj.getPassword() + "', " +
                "address = ' " + studentObj.getAddress() + "', " +
                "email = '" + student.getEmail() + "', " +
                "phoneNo = '" + studentObj.getPhoneNo() + "', " +
                "type = '" + studentObj.getType() + "' " +
                " WHERE id = " + student.getId();

        String queryStudent = "UPDATE Student SET " +
                "studentNo = '" + studentObj.getStudentNo() + "', " +
                "teamId = '" + studentObj.getTeamId().getId() + "'" +
                " WHERE studentId = " + studentObj.getStudentId();

        System.out.println(queryPerson);
        System.out.println(queryStudent);

        try {
            Statement stmtPerson = conn.createStatement();
            stmtPerson.setQueryTimeout(5);
            stmtPerson.executeUpdate(queryPerson);
            stmtPerson.close();

            Statement stmtStudent = conn.createStatement();
            stmtStudent.setQueryTimeout(5);
            rc = stmtStudent.executeUpdate(queryStudent);
            stmtStudent.close();
        }
        catch (Exception e) {
            System.out.println("Update exception in Student db :" +e);
        }
        return rc;
    }

    @Override
    public int deleteStudent(int id) {
        int rc = -1;

        String deleteStudent = "DELETE FROM Student WHERE studentId = '" + id + "'";
        String deletePerson = "DELETE FROM Person WHERE id = '" + id + "'";

        try {
            connectionDB.startTransaction();
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(deleteStudent);
            rc = stmt.executeUpdate(deletePerson);
            stmt.close();
            connectionDB.commitTransaction();
        }
        catch (Exception e) {
            System.out.println(e);
            connectionDB.rollbackTransaction();
        }

        return rc;

    }

    //Finding a single Student
    private Student singleWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        Student studentObj = new Student();

        String query = buildQuery(wClause);
        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next()) {
                studentObj = buildStudent(results);
                stmt.close();
            }
            else {
                studentObj = null;
            }
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
        }

        return studentObj;
    }

    //Finding all Students
    private ArrayList<Student> miscWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        ArrayList<Student> list = new ArrayList<Student>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()) {
                Student studentObj = buildStudent(results);
                list.add(studentObj);
            }
            System.out.println(query);

            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
            e.printStackTrace();
        }

        return list;
    }


    //Method to build the query
    private String buildQuery(String wClause) {
        String query = "SELECT id, studentId, name, username, password, address, email, phoneNo, type, studentNo, teamId FROM Student, Person";

        if (wClause.length()>0) {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    //Method to build a Student object
    private Student buildStudent(ResultSet results) {

        Student studentObj = new Student();
        TeamDB teamDB = new TeamDB();

        try {
            studentObj.setId(results.getInt("id"));
            studentObj.setName(results.getString("name"));
            studentObj.setUsername(results.getString("username"));
            studentObj.setPassword(results.getString("password"));
            studentObj.setAddress(results.getString("address"));
            studentObj.setEmail(results.getString("email"));
            studentObj.setPhoneNo(results.getInt("phoneNo"));
            studentObj.setType(results.getString("type"));
            studentObj.setStudentId(results.getInt("studentId"));
            studentObj.setStudentNo(results.getInt("studentNo"));
            studentObj.setTeamId(teamDB.findTeam(results.getInt("teamId")));
        }
        catch (Exception e) {
            System.out.println("Error building Student object" + e);
        }

        return studentObj;
    }

    public int studentGetMax() {
        int nextId = GetMax.getMaxId("SELECT max(id) from Person");

        nextId = nextId +1;

        return nextId;
    }
}
